﻿unit cardmap;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ExtCtrls, DB, ADODB, cxGraphics, cxControls,
  cxLookAndFeels, cxLookAndFeelPainters, cxStyles, dxSkinsCore,
  dxSkinsDefaultPainters, dxSkinscxPCPainter, cxCustomData, cxFilter, cxData,
  cxDataStorage, cxEdit, cxNavigator, cxDBData, cxGridCustomTableView,
  cxGridTableView, cxGridDBTableView, cxGridLevel, cxClasses, cxGridCustomView,
  cxGrid, cxImage, cxCheckBox, cxBlobEdit,jpeg, cxEditRepositoryItems,
  dxLayoutContainer, cxGridLayoutView, cxGridDBLayoutView,
  cxGridCustomLayoutView, cxGridCardView, cxGridDBCardView, cxLabel, cxTextEdit,
  cxGridExportLink,Wincodec, dxPSGlbl, dxPSUtl, dxPrnPg, dxBkgnd, dxWrap,
  dxPrnDev, dxPgsDlg, dxPSCore, cxPCdxBarPopupMenu, cxPC, MemDS, DBAccess, Uni,
  cxMemo, cxDropDownEdit, cxMRUEdit, cxContainer, cxDBEdit, cxDBNavigator,math,StrUtils,
  cxRadioGroup, cxGroupBox;

type
  TfrmCardmap = class(TForm)
    con1: TADOConnection;
    qry1: TADOQuery;
    ds1: TDataSource;
    qry1Id: TAutoIncField;
    qry1已采: TBooleanField;
    qry1已发: TBooleanField;
    qry1标题: TWideMemoField;
    qry1内容: TWideMemoField;
    qry1编号: TWideMemoField;
    qry1分组id: TWideMemoField;
    qry1timecreate: TWideMemoField;
    qry1front_pic: TWideMemoField;
    qry1PageUrl: TWideMemoField;
    dlgOpen1: TOpenDialog;
    FileOpenDialog1: TFileOpenDialog;
    qry2: TADOQuery;
    qry1onlyocrthis: TBooleanField;
    qry1back_pic: TWideMemoField;
    qry1donotorc: TBooleanField;
    qry1front_binary: TBlobField;
    StyleRepository: TcxStyleRepository;
    cxStyle1: TcxStyle;
    cxStyle2: TcxStyle;
    cxStyle3: TcxStyle;
    cxStyle4: TcxStyle;
    cxStyle5: TcxStyle;
    cxStyle6: TcxStyle;
    cxStyle7: TcxStyle;
    cxStyle8: TcxStyle;
    cxStyle9: TcxStyle;
    cxStyle10: TcxStyle;
    cxStyle11: TcxStyle;
    cxStyle12: TcxStyle;
    cxStyle13: TcxStyle;
    cxStyle14: TcxStyle;
    cxStyle15: TcxStyle;
    cxStyle16: TcxStyle;
    cxStyle17: TcxStyle;
    cxStyle18: TcxStyle;
    cxStyle19: TcxStyle;
    cxStyle20: TcxStyle;
    cxStyle21: TcxStyle;
    cxStyle22: TcxStyle;
    cxStyle23: TcxStyle;
    cxStyle24: TcxStyle;
    stValues: TcxStyle;
    stItems: TcxStyle;
    stHeader: TcxStyle;
    stRecordCaption: TcxStyle;
    stRecordSelected: TcxStyle;
    GridTableViewStyleSheetDevExpress: TcxGridTableViewStyleSheet;
    GridCardViewStyleSheetDevExpress: TcxGridCardViewStyleSheet;
    EditRepository: TcxEditRepository;
    EditRepositoryImage: TcxEditRepositoryImageItem;
    EditRepositoryMemo: TcxEditRepositoryMemoItem;
    EditRepositoryHyperLink: TcxEditRepositoryHyperLinkItem;
    EditRepositoryPrice: TcxEditRepositoryCurrencyItem;
    EditRepositoryAutomatic: TcxEditRepositoryCheckBoxItem;
    pnl2: TPanel;
    grp3: TGroupBox;
    edtmdbname: TEdit;
    btn1: TButton;
    edtpicpath: TEdit;
    btn2: TButton;
    btnsaveimg: TButton;
    btn5: TButton;
    btn6: TButton;
    btn3: TButton;
    btn4: TButton;
    cxGrid1: TcxGrid;
    cxGrid1DBCardView1: TcxGridDBCardView;
    cxGrid1DBCardView1front_binary: TcxGridDBCardViewRow;
    cxGrid1DBCardView1Id: TcxGridDBCardViewRow;
    cxGrid1DBCardView1DBCardViewRow: TcxGridDBCardViewRow;
    cxGrid1DBCardView1DBCardViewRow1: TcxGridDBCardViewRow;
    cxGrid1DBCardView1DBCardViewRow2: TcxGridDBCardViewRow;
    cxGrid1DBCardView1DBCardViewRow3: TcxGridDBCardViewRow;
    cxGrid1DBCardView1DBCardViewRow4: TcxGridDBCardViewRow;
    cxGrid1DBCardView1id1: TcxGridDBCardViewRow;
    cxGrid1DBCardView1timecreate: TcxGridDBCardViewRow;
    cxGrid1DBCardView1front_pic: TcxGridDBCardViewRow;
    cxGrid1DBCardView1back_pic: TcxGridDBCardViewRow;
    cxGrid1DBCardView1PageUrl: TcxGridDBCardViewRow;
    cxGrid1DBCardView1donotorc: TcxGridDBCardViewRow;
    cxGrid1DBCardView1onlyocrthis: TcxGridDBCardViewRow;
    cxGrid1DBCardView1mergnum: TcxGridDBCardViewRow;
    cxGrid1Level2: TcxGridLevel;
    qry1front_angle: TWideStringField;
    cxGrid1DBCardView1front_angle: TcxGridDBCardViewRow;
    grp4: TGroupBox;
    qry1group_name: TWideStringField;
    cxGrid1DBCardView1group_name: TcxGridDBCardViewRow;
    lbl1: TLabel;
    edtbatgroupname: TEdit;
    btn7: TButton;
    btn8: TButton;
    btn9: TButton;
    qry1mergnum: TIntegerField;
    pnl1: TPanel;
    btn10: TButton;
    cxGrid2Level1: TcxGridLevel;
    cxGrid2: TcxGrid;
    btn11: TButton;
    btn12: TButton;
    ds2: TDataSource;
    qry3: TADOQuery;
    qry3username: TWideStringField;
    qry3mobile: TWideStringField;
    qry3tel: TWideStringField;
    qry3fax: TWideStringField;
    qry3email: TWideStringField;
    qry3website: TWideStringField;
    qry3qq: TWideStringField;
    qry3country: TWideStringField;
    qry3state: TWideStringField;
    qry3city: TWideStringField;
    qry3street1: TWideStringField;
    qry3street2: TWideStringField;
    qry3zip: TWideStringField;
    qry3company: TWideStringField;
    qry3department: TWideStringField;
    qry3title: TWideStringField;
    qry3jsonstr: TWideMemoField;
    qry3ocrtext: TWideMemoField;
    qry3group_name: TWideStringField;
    qry3front_binary: TBlobField;
    qry3back_binary: TBlobField;
    cxGrid2DBCardView1: TcxGridDBCardView;
    cxGrid2DBCardView1username: TcxGridDBCardViewRow;
    cxGrid2DBCardView1mobile: TcxGridDBCardViewRow;
    cxGrid2DBCardView1tel: TcxGridDBCardViewRow;
    cxGrid2DBCardView1fax: TcxGridDBCardViewRow;
    cxGrid2DBCardView1email: TcxGridDBCardViewRow;
    cxGrid2DBCardView1website: TcxGridDBCardViewRow;
    cxGrid2DBCardView1qq: TcxGridDBCardViewRow;
    cxGrid2DBCardView1country: TcxGridDBCardViewRow;
    cxGrid2DBCardView1state: TcxGridDBCardViewRow;
    cxGrid2DBCardView1city: TcxGridDBCardViewRow;
    cxGrid2DBCardView1street1: TcxGridDBCardViewRow;
    cxGrid2DBCardView1street2: TcxGridDBCardViewRow;
    cxGrid2DBCardView1zip: TcxGridDBCardViewRow;
    cxGrid2DBCardView1company: TcxGridDBCardViewRow;
    cxGrid2DBCardView1department: TcxGridDBCardViewRow;
    cxGrid2DBCardView1title: TcxGridDBCardViewRow;
    cxGrid2DBCardView1jsonstr: TcxGridDBCardViewRow;
    cxGrid2DBCardView1ocrtext: TcxGridDBCardViewRow;
    cxGrid2DBCardView1group_name: TcxGridDBCardViewRow;
    cxGrid2DBCardView1front_binary: TcxGridDBCardViewRow;
    cxGrid2DBCardView1back_binary: TcxGridDBCardViewRow;
    dlgPnt1: TPrintDialog;
    dlgSave1: TSaveDialog;
    cxGrid2Level2: TcxGridLevel;
    cxGrid2DBTableView1: TcxGridDBTableView;
    cxGrid2DBTableView1username: TcxGridDBColumn;
    cxGrid2DBTableView1mobile: TcxGridDBColumn;
    cxGrid2DBTableView1tel: TcxGridDBColumn;
    cxGrid2DBTableView1fax: TcxGridDBColumn;
    cxGrid2DBTableView1email: TcxGridDBColumn;
    cxGrid2DBTableView1website: TcxGridDBColumn;
    cxGrid2DBTableView1qq: TcxGridDBColumn;
    cxGrid2DBTableView1country: TcxGridDBColumn;
    cxGrid2DBTableView1state: TcxGridDBColumn;
    cxGrid2DBTableView1city: TcxGridDBColumn;
    cxGrid2DBTableView1street1: TcxGridDBColumn;
    cxGrid2DBTableView1street2: TcxGridDBColumn;
    cxGrid2DBTableView1zip: TcxGridDBColumn;
    cxGrid2DBTableView1company: TcxGridDBColumn;
    cxGrid2DBTableView1department: TcxGridDBColumn;
    cxGrid2DBTableView1title: TcxGridDBColumn;
    cxGrid2DBTableView1jsonstr: TcxGridDBColumn;
    cxGrid2DBTableView1ocrtext: TcxGridDBColumn;
    cxGrid2DBTableView1group_name: TcxGridDBColumn;
    cxGrid2DBTableView1front_binary: TcxGridDBColumn;
    cxGrid2DBTableView1back_binary: TcxGridDBColumn;
    cxPageControl1: TcxPageControl;
    cxTabSheet1: TcxTabSheet;
    cxTabSheet2: TcxTabSheet;
    pnl4: TPanel;
    qry3jsonstrback: TWideMemoField;
    qry3ocrtextback: TWideMemoField;
    qry1back_binary: TBlobField;
    qry1group_id: TWideStringField;
    cxGrid2DBCardView2: TcxGridDBCardView;
    cxGrid2Level3: TcxGridLevel;
    cxGrid2DBCardView3: TcxGridDBCardView;
    qry4: TADOQuery;
    qry4group_name: TWideStringField;
    qry4front_binary: TBlobField;
    qry4back_binary: TBlobField;
    ds3: TDataSource;
    cxGrid2DBCardView3group_name: TcxGridDBCardViewRow;
    cxGrid2DBCardView3front_binary: TcxGridDBCardViewRow;
    cxGrid2DBCardView3back_binary: TcxGridDBCardViewRow;
    qry21: TADOQuery;
    myqry1: TUniQuery;
    myqry2: TUniQuery;
    qry3contact_id: TWideStringField;
    qry3timecreate: TWideStringField;
    qry4gid: TWideStringField;
    qry4timecreate: TWideStringField;
    cxGrid2DBCardView1contact_id: TcxGridDBCardViewRow;
    cxGrid2DBCardView1gid: TcxGridDBCardViewRow;
    cxGrid2DBCardView1timecreate: TcxGridDBCardViewRow;
    cxGrid2DBCardView1jsonstrback: TcxGridDBCardViewRow;
    cxGrid2DBCardView1ocrtextback: TcxGridDBCardViewRow;
    cxGrid2DBTableView1contact_id: TcxGridDBColumn;
    cxGrid2DBTableView1gid: TcxGridDBColumn;
    cxGrid2DBTableView1timecreate: TcxGridDBColumn;
    cxGrid2DBTableView1jsonstrback: TcxGridDBColumn;
    cxGrid2DBTableView1ocrtextback: TcxGridDBColumn;
    cxGrid2DBCardView3gid: TcxGridDBCardViewRow;
    cxGrid2DBCardView3timecreate: TcxGridDBCardViewRow;
    qry3gid: TWideStringField;
    cxDBNavigator1: TcxDBNavigator;
    lbl12: TLabel;
    mmostreet1: TMemo;
    lbl15: TLabel;
    mmocompany: TMemo;
    lbl16: TLabel;
    mmodept: TMemo;
    lbl17: TLabel;
    mmotitle: TMemo;
    lbl3: TLabel;
    edt3: TEdit;
    lblqry1cnt: TLabel;
    btn13: TButton;
    cxRadioGroup1: TcxRadioGroup;
    cxRadioButton1: TcxRadioButton;
    cxRadioButton2: TcxRadioButton;
    procedure btn1Click(Sender: TObject);
    procedure btn2Click(Sender: TObject);
    procedure cxGrid1DBTableView1CustomDrawIndicatorCell(
      Sender: TcxGridTableView; ACanvas: TcxCanvas;
      AViewInfo: TcxCustomGridIndicatorItemViewInfo; var ADone: Boolean);
    procedure btn3Click(Sender: TObject);
    procedure btn4Click(Sender: TObject);
    procedure btn5Click(Sender: TObject);
    procedure btnsaveimgClick(Sender: TObject);
    procedure btn7Click(Sender: TObject);
    procedure btn9Click(Sender: TObject);
    procedure btn8Click(Sender: TObject);
    procedure btn10Click(Sender: TObject);
    procedure btn11Click(Sender: TObject);
    procedure btn12Click(Sender: TObject);
    procedure qry1AfterOpen(DataSet: TDataSet);
    procedure btn13Click(Sender: TObject);
    procedure cxRadioButton1Click(Sender: TObject);
    procedure cxRadioButton2Click(Sender: TObject);
    procedure edtpicpathChange(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frmCardmap: TfrmCardmap;

implementation
uses generics.Collections,global,dm;

{$R *.dfm}

procedure TfrmCardmap.btn10Click(Sender: TObject);
var
  jsonmap,jsonmap1 : TDictionary<string,string>;
  contact_id,group_id,username,cardsource,cardstate,cloudcheck,sync_cid,timecreate : String;
  mobile,tel,fax,email,website,qq : String;
  country,statestr,city,street1,street2,zip : String;
  company,department,title : String;
  front_pic_url,front_pic_angle,front_pic_value,back_pic_url,back_pic_angle,back_pic_value : String;
  jsonstr,url,ocrtext,jsonstrback,ocrtextback,samecardid : String;
  cnt : Integer;
  mergnum : Integer;
  gid : String;
  ms,msfront:TMemoryStream;
  ischinese : Boolean;
  sl : TStringList;
  i : Integer;
begin
  //判断配对标记是否都已设
  with qry2 do
  begin
    close;
    SQL.Text := 'select id from content where onlyocrthis<>-1 and mergnum=0';
    open;
  end;
  if qry2.RecordCount>0 then
  begin
    ShowMessage('有记录没有设好配对，请设好配对后再继续!');
    exit;
  end;
  with qry2 do
  begin
    sql.Text := 'delete from tcardsgroup';
    ExecSQL;
  end;
  with qry2 do
  begin
    sql.Text := 'delete from tcards';
    ExecSQL;
  end;
  gid := '';
  mergnum := 0;
  jsonmap := TDictionary<string,string>.Create;
  jsonmap1 := TDictionary<string,string>.Create;
  qry1.First;
  while not qry1.Eof do
  begin
    //记录不是分组标记
    if not qry1.FieldByName('donotorc').AsBoolean then
    begin
      //不配对记录和配对第2条记录
      if qry1.FieldByName('onlyocrthis').AsBoolean or (mergnum=qry1.FieldByName('mergnum').AsInteger) then
      begin
        with qry2 do
        begin
          if not qry1.FieldByName('onlyocrthis').AsBoolean then
            jsonmap1 := json2map(qry1.fieldbyName('内容').AsString);
          sql.Text := 'insert into tcards ('
          +'contact_id,username,mobile,tel,fax,email,website,qq,country,state,city,'
          +'street1,street2,zip,company,department,title,ocrtext,jsonstr,'
          +'ocrtextback,jsonstrback,timecreate,'
          +'front_binary,back_binary,gid,group_name'
          +') values ('
          +':contact_id,:username,:mobile,:tel,:fax,:email,:website,:qq,:country,:state,:city,'
          +':street1,:street2,:zip,:company,:department,:title,:ocrtext,:jsonstr,'
          +':ocrtextback,:jsonstrback,:timecreate,'
          +':front_binary,:back_binary,:gid,:group_name'
          +')';
          if includechinese(jsonmap['username']+jsonmap1['username']+jsonmap['street1']+jsonmap1['street1']+jsonmap['company']+jsonmap1['company']) then
            ischinese := True
          else
            ischinese := false;
          Parameters.ParamByName('username').Value := IfThen(jsonmap['username']<>'',jsonmap['username'], jsonmap1['username']);
          Parameters.ParamByName('mobile').Value := IfThen(jsonmap['mobile']<>'',jsonmap['mobile'], jsonmap1['mobile']);
          Parameters.ParamByName('tel').Value := IfThen(jsonmap['tel']<>'',jsonmap['tel'], jsonmap1['tel']);
          Parameters.ParamByName('fax').Value := IfThen(jsonmap['fax']<>'',jsonmap['fax'], jsonmap1['fax']);
          Parameters.ParamByName('email').Value := IfThen(jsonmap['email']<>'',jsonmap['email'], jsonmap1['email']);
          Parameters.ParamByName('website').Value := IfThen(jsonmap['website']<>'',jsonmap['website'], jsonmap1['website']);
          Parameters.ParamByName('qq').Value := IfThen(jsonmap['qq']<>'',jsonmap['qq'], jsonmap1['qq']);
          Parameters.ParamByName('country').Value := IfThen(jsonmap['country']<>'',jsonmap['country'], jsonmap1['country']);
          Parameters.ParamByName('state').Value := IfThen(jsonmap['state']<>'',jsonmap['state'], jsonmap1['state']);
          Parameters.ParamByName('city').Value := IfThen(jsonmap['city']<>'',jsonmap['city'], jsonmap1['city']);
          //地址和公司名再合并一次，如果正反面有中文字，公司名去掉英文的，地址也去掉英文的。
          if ischinese and (not includechinese(jsonmap['street1'])) and (jsonmap1['street1']<>'') then
            jsonmap['street1'] := jsonmap1['street1'];
          //处理多个地址，第一个放在street1,后面的放在street2里面
          if jsonmap['street1']<>'' then
          begin
            sl := TStringList.Create;
            //sl.Delimiter := #13#10;
            //sl.DelimitedText := jsonmap['street1'];
            sl.Text := jsonmap['street1'];
            for i := 0 to sl.Count - 1 do
            begin
              if i=0 then
                 jsonmap['street1'] := sl.Strings[0]
              else
                 jsonmap['street2'] := jsonmap['street2']+#13#10+sl.Strings[i];
            end;
            jsonmap['street2'] := StringReplace(jsonmap['street2'],#13#10,' ',[rfReplaceAll]);
            sl.free;
          end;
          Parameters.ParamByName('street1').Value := jsonmap['street1'];
          Parameters.ParamByName('street2').Value := jsonmap['street2'];
          Parameters.ParamByName('zip').Value := IfThen(jsonmap['zip']<>'',jsonmap['zip'], jsonmap1['zip']);
          if ischinese and (not includechinese(jsonmap['company'])) and (jsonmap1['company']<>'') then
            jsonmap['company'] := jsonmap1['company'];
          //处理多个公司名，如果公司名称包含公司两个字，就取它，否则取第一个
          if jsonmap['company']<>'' then
          begin
            sl := TStringList.Create;
            cnt := 0;
            sl.Text := jsonmap['company'];
            jsonmap['company'] := sl.Strings[0];
            for i := 0 to sl.Count - 1 do
              if pos('公司',sl.strings[i])>0 then
              begin
                jsonmap['company'] := sl.Strings[i];
                break;
              end;
            sl.free;
          end;
          Parameters.ParamByName('company').Value := jsonmap['company'];
          Parameters.ParamByName('department').Value := IfThen(jsonmap['department']<>'',jsonmap['department'], jsonmap1['department']);
          Parameters.ParamByName('title').Value := IfThen(jsonmap['title']<>'',jsonmap['title'], jsonmap1['title']);
          Parameters.ParamByName('ocrtext').Value := jsonmap['ocrtext'];
          Parameters.ParamByName('jsonstr').Value := jsonmap['jsonstr'];
          Parameters.ParamByName('contact_id').Value := jsonmap['contact_id'];
          Parameters.ParamByName('timecreate').Value := jsonmap['timecreate'];
          Parameters.ParamByName('gid').Value := gid;
          Parameters.ParamByName('group_name').Value := qry1.FieldByName('group_name').AsString;
          if qry1.FieldByName('onlyocrthis').AsBoolean then
          begin
            ms:=TMemoryStream.Create;
            TBlobField(qry1.FieldByName('front_binary')).SaveToStream(ms);
            ms.Position:=0;
            Parameters.ParamByName('front_binary').LoadFromStream(ms,ftBlob);
            ms.Free;
            ms:=TMemoryStream.Create ;
            TBlobField(qry1.FieldByName('back_binary')).SaveToStream(ms);
            ms.Position:=0;
            Parameters.ParamByName('back_binary').LoadFromStream(ms,ftBlob);
            ms.Free;
          end
          else
          begin
            //名片反面
            Parameters.ParamByName('ocrtextback').Value := jsonmap1['ocrtext'];
            Parameters.ParamByName('jsonstrback').Value := jsonmap1['jsonstr'];
            msfront.Position:=0;
            Parameters.ParamByName('front_binary').LoadFromStream(msfront,ftBlob);
            msfront.Free;
            ms:=TMemoryStream.Create ;
            TBlobField(qry1.FieldByName('front_binary')).SaveToStream(ms);
            ms.Position:=0;
            Parameters.ParamByName('back_binary').LoadFromStream(ms,ftBlob);
            ms.Free;
          end;
          ExecSQL;
        end;
      end
      else
      begin
        //配对记录的正面
        jsonmap := json2map(qry1.fieldbyName('内容').AsString);
        msfront:=TMemoryStream.Create;
        TBlobField(qry1.FieldByName('front_binary')).SaveToStream(msfront);
      end;
      mergnum := qry1.FieldByName('mergnum').AsInteger;
    end
    else
    //记录是分组标记的
    begin
      if mergnum=qry1.FieldByName('mergnum').AsInteger then
      begin
        //修改当前的tcardsgroup.back_binary，写入反面图片
        with qry2 do
        begin
          sql.Text := 'update tcardsgroup set back_binary=:back_binary where gid=:gid';
          Parameters.ParamByName('gid').Value := gid;
          ms:=TMemoryStream.Create;
          TBlobField(qry1.FieldByName('front_binary')).SaveToStream(ms);
          ms.Position:=0;
          Parameters.ParamByName('back_binary').LoadFromStream(ms,ftBlob);
          ms.Free;
          ExecSQL;
        end;
      end
      else
      begin
        //新增tcardsgroup记录，并将图片写入front_binary
        mergnum := qry1.FieldByName('mergnum').AsInteger;
        gid := qry1.FieldByName('编号').AsString;
        with qry2 do
        begin
          sql.Text := 'insert into tcardsgroup (gid,group_name,timecreate,front_binary)'
            +' values (:gid,:group_name,:timecreate,:front_binary)';
          Parameters.ParamByName('gid').Value := qry1.FieldByName('编号').AsString;
          Parameters.ParamByName('group_name').Value := qry1.FieldByName('group_name').AsString;
          Parameters.ParamByName('timecreate').Value := qry1.FieldByName('timecreate').AsString;
          ms:=TMemoryStream.Create;
          TBlobField(qry1.FieldByName('front_binary')).SaveToStream(ms);
          ms.Position:=0;
          Parameters.ParamByName('front_binary').LoadFromStream(ms,ftBlob);
          ms.Free;
          ExecSQL;
        end;
      end;
    end;
    qry1.Next;
  end;
  jsonmap.Free;
  jsonmap1.Free;
  ShowMessage('执行完毕，到识别后的数据页查看识别情况');
  qry3.Close;
  qry3.Open;
  qry4.Close;
  qry4.Open;
end;

procedure TfrmCardmap.btn11Click(Sender: TObject);
begin
  dlgSave1.Filter := '*.xls';
  if dlgSave1.Execute then
  begin
   ExportGridToExcel(dlgSave1.FileName,cxGrid2,True,True,True);
   {
    DevExpress.XtraPrinting.XlsExportOptions options = new DevExpress.XtraPrinting.XlsExportOptions();
    gridControl1.ExportToXls(dlgSave1.FileName);
    DevExpress.XtraEditors.XtraMessageBox.Show("保存成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); 
    }
  end;
 end;

procedure TfrmCardmap.btn12Click(Sender: TObject);
var
  mes : TMemoryStream;
  gid : integer;
begin
  //删除mysql里面有相同的分组信息和名片信息
  qry3.First;
  while not qry3.eof do
  begin
    with myqry1 do
    begin
      sql.Text := 'delete from camcards_cards where contact_id=:contact_id';
      ParamByName('contact_id').Value := qry3.FieldByName('contact_id').AsString;
      ExecSQL;
    end;
    qry3.Next;
  end;
  qry4.First;
  while not qry4.eof do
  begin
    with myqry1 do
    begin
      sql.Text := 'delete from camcards_cardsgroup where gid=:gid';
      ParamByName('gid').Value := qry4.FieldByName('gid').AsString;
      ExecSQL;
    end;
    qry4.Next;
  end;
  //向mysql插入名片记录
  qry3.First;
  while not qry3.eof do
  begin
    with myqry1 do
    begin
      SQL.text := 'insert into camcards_cards ('
      +'contact_id,gid,'
      //+'group_id,cardsource,cardstate,cloudcheck,sync_cid,'
      +'timecreate,username,mobile,tel,fax,'
      +'email,website,qq,country,state,'
      +'city,street1,street2,zip,company,'
      +'department,title,jsonstr,ocrtext,jsonstrback,'
      +'ocrtextback,front_binary,back_binary'
      +') values ('
      +':contact_id,:gid,'
      //+':group_id,:cardsource,:cardstate,:cloudcheck,:sync_cid,'
      +':timecreate,:username,:mobile,:tel,:fax,'
      +':email,:website,:qq,:country,:state,'
      +':city,:street1,:street2,:zip,:company,'
      +':department,:title,:jsonstr,:ocrtext,:jsonstrback,'
      +':ocrtextback,:front_binary,:back_binary'
      +')';
      ParamByName('contact_id').value := qry3.FieldByName('contact_id').AsVariant;
      ParamByName('gid').value := qry3.FieldByName('gid').AsString;
      {
      ParamByName('group_id').value := qry3.FieldByName('group_id').AsString;
      ParamByName('cardsource').value := qry3.FieldByName('cardsource').AsString;
      ParamByName('cardstate').value := qry3.FieldByName('cardstate').AsString;
      ParamByName('cloudcheck').value := qry3.FieldByName('cloudcheck').AsString;
      ParamByName('sync_cid').value := qry3.FieldByName('sync_cid').AsString;
      }
      ParamByName('timecreate').value := qry3.FieldByName('timecreate').AsString;
      ParamByName('username').value := qry3.FieldByName('username').AsString;
      ParamByName('mobile').value := qry3.FieldByName('mobile').AsString;
      ParamByName('tel').value := qry3.FieldByName('tel').AsString;
      ParamByName('fax').value := qry3.FieldByName('fax').AsString;
      ParamByName('email').value := qry3.FieldByName('email').AsString;
      ParamByName('website').value := qry3.FieldByName('website').AsString;
      ParamByName('qq').value := qry3.FieldByName('qq').AsString;
      ParamByName('country').value := qry3.FieldByName('country').AsString;
      ParamByName('state').value := qry3.FieldByName('state').AsString;
      ParamByName('city').value := qry3.FieldByName('city').AsString;
      ParamByName('street1').value := qry3.FieldByName('street1').AsString;
      ParamByName('street2').value := qry3.FieldByName('street2').AsString;
      ParamByName('zip').value := qry3.FieldByName('zip').AsString;
      ParamByName('company').value := qry3.FieldByName('company').AsString;
      ParamByName('department').value := qry3.FieldByName('department').AsString;
      ParamByName('title').value := qry3.FieldByName('title').AsString;
      ParamByName('jsonstr').value := qry3.FieldByName('jsonstr').AsString;
      ParamByName('ocrtext').value := qry3.FieldByName('ocrtext').AsString;
      ParamByName('jsonstrback').value := qry3.FieldByName('jsonstrback').AsString;
      ParamByName('ocrtextback').value := qry3.FieldByName('ocrtextback').AsString;
      mes := TMemoryStream.Create;
      TBlobField(qry3.FieldByName('front_binary')).SaveToStream(mes);
      mes.Position := 0;
      ParamByName('front_binary').loadfromstream(mes,ftBlob);
      mes.Free;
      mes := TMemoryStream.Create;
      TBlobField(qry3.FieldByName('back_binary')).SaveToStream(mes);
      mes.Position := 0;
      ParamByName('back_binary').loadfromstream(mes,ftBlob);
      mes.Free;
      ExecSQL;
    end;
    //判断相同的名片 手机+姓名相同
    if (qry3.FieldByName('username').AsString<>'') and (qry3.FieldByName('mobile').AsString<>'') then
    begin
      with myqry1 do
      begin
        Close;
        SQL.Text := 'select contact_id from camcards_cards where contact_id=samecardid and username=:username and mobile=:mobile';
        ParamByName('username').Value := qry3.FieldByName('username').AsString;
        ParamByName('mobile').Value := qry3.FieldByName('mobile').AsString;
        open;
      end;
      if myqry1.Eof  then
      begin
        with myqry2 do
        begin
          sql.Text := 'update camcards_cards set samecardid=:samecardid where contact_id=:contact_id';
          ParamByName('samecardid').Value := qry3.FieldByName('contact_id').AsString;
          ParamByName('contact_id').Value := qry3.FieldByName('contact_id').AsString;
          ExecSQL;
        end;
      end
      else
      begin
        with myqry2 do
        begin
          sql.Text := 'update camcards_cards set samecardid=:samecardid where contact_id=:contact_id';
          ParamByName('samecardid').Value := myqry1.FieldByName('contact_id').AsString;
          ParamByName('contact_id').Value := qry3.FieldByName('contact_id').AsString;
          ExecSQL;
        end;
      end;
    end
    else
    begin
        with myqry2 do
        begin
          sql.Text := 'update camcards_cards set samecardid=:samecardid where contact_id=:contact_id';
          ParamByName('samecardid').Value := qry3.FieldByName('contact_id').AsString;
          ParamByName('contact_id').Value := qry3.FieldByName('contact_id').AsString;
          ExecSQL;
        end;
    end;
    qry3.Next;
  end;
  //插入分组记录
  qry4.First;
  while not qry4.Eof do
  begin
    with myqry1 do
    begin
      sql.Text := 'insert into camcards_cardsgroup (gid,group_name,timecreate,front_binary,back_binary)'
      +' values (:gid,:group_name,:timecreate,:front_binary,:back_binary)';
      ParamByName('gid').value := qry4.FieldByName('gid').AsString;
      ParamByName('group_name').value := qry4.FieldByName('group_name').AsString;
      ParamByName('timecreate').value := qry4.FieldByName('timecreate').AsString;
      mes := TMemoryStream.Create;
      TBlobField(qry4.FieldByName('front_binary')).SaveToStream(mes);
      mes.Position := 0;
      ParamByName('front_binary').loadfromstream(mes,ftBlob);
      mes.Free;
      mes := TMemoryStream.Create;
      TBlobField(qry4.FieldByName('back_binary')).SaveToStream(mes);
      mes.Position := 0;
      ParamByName('back_binary').loadfromstream(mes,ftBlob);
      mes.Free;
      ExecSQL;
    end;
    qry4.Next;
  end;
  ShowMessage('已向vtiger提交了全部识别记录。');
end;

procedure TfrmCardmap.btn13Click(Sender: TObject);
var
  jsonmap : TDictionary<string,string>;
  picname : String;
  angle : Integer;
begin
  if qry1.Eof then
    exit;
  jsonmap := TDictionary<string,string>.Create;
  jsonmap := json2map(qry1.fieldbyName('内容').AsString);
  edt3.text := jsonmap['username'];
  mmostreet1.lines.text := jsonmap['street1'];
  mmocompany.lines.text := jsonmap['company'];
  mmodept.lines.text := jsonmap['department'];
  mmotitle.lines.text := jsonmap['title'];
end;

procedure TfrmCardmap.btn1Click(Sender: TObject);
var
  filestr : string;
  strlist : TStringList;
begin
  if dlgOpen1.Execute  then
  begin
    filestr := dlgOpen1.Files[0];
    edtmdbname.Text := filestr;
  end
  else
    exit;
  if con1.Connected then con1.Connected := false;
  con1.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+filestr+';Persist Security Info=False';
  con1.Connected := true;
  strlist := TStringList.Create;
  con1.GetFieldNames('content', strlist);
  if strlist.IndexOf('donotorc')=-1 then
  begin
    with qry2 do
    begin
      SQL.Text := 'ALTER TABLE content ADD COLUMN donotorc BIT';
      ExecSQL;
    end;
  end;
  if strlist.IndexOf('onlyocrthis')=-1 then
  begin
    with qry2 do
    begin
      SQL.Text := 'ALTER TABLE content ADD COLUMN onlyocrthis BIT';
      ExecSQL;
    end;
  end;
  if strlist.IndexOf('mergnum')=-1 then
  begin
    with qry2 do
    begin
      SQL.Text := 'ALTER TABLE content ADD COLUMN mergnum int 0';
      ExecSQL;
    end;
  end;
  if strlist.IndexOf('timecreate')=-1 then
  begin
    with qry2 do
    begin
      SQL.Text := 'ALTER TABLE content ADD COLUMN timecreate Memo';
      ExecSQL;
      SQL.Text := 'update content set timecreate='
      +'Mid(内容,InStr(内容,'+quotedStr('"timecreate":"')+')+14,InStr(InStr(内容,'+quotedStr('"timecreate":"')+')+14,内容,'+quotedStr('"')+')-InStr(内容,'+quotedStr('"timecreate":"')+')-14)';
      ExecSQL;
    end;
  end;
  if strlist.IndexOf('front_pic')=-1 then
  begin
    with qry2 do
    begin
      SQL.Text := 'ALTER TABLE content ADD COLUMN front_pic Memo NULL';
      ExecSQL;
      SQL.Text := 'update content set front_pic='
      +'Mid(内容,InStr(内容,'+quotedStr('"front_pic","value":"')+')+21,InStr(InStr(内容,'+quotedStr('"front_pic","value":"')+')+21,内容,'+quotedStr('"')+')-InStr(内容,'+quotedStr('"front_pic","value":"')+')-21)';
      ExecSQL;
    end;
  end;
  if strlist.IndexOf('front_angle')=-1 then
  begin
    with qry2 do
    begin
      SQL.Text := 'ALTER TABLE content ADD COLUMN front_angle varchar(100)';
      ExecSQL;
      SQL.Text := 'update content set front_angle='
      +'Mid(内容,InStr(内容,'+quotedStr('"angle":"')+')+9,InStr(InStr(内容,'+quotedStr('"angle":"')+')+9,内容,'+quotedStr('"')+')-InStr(内容,'+quotedStr('"angle":"')+')-9)';
      ExecSQL;
    end;
  end;
  if strlist.IndexOf('back_pic')=-1 then
  begin
    with qry2 do
    begin
      SQL.Text := 'ALTER TABLE content ADD COLUMN back_pic Memo NULL';
      ExecSQL;
      SQL.Text := 'update content set back_pic='
      +'Mid(内容,InStr(内容,'+quotedStr('"back_pic","value":"')+')+20,InStr(InStr(内容,'+quotedStr('"back_pic","value":"')+')+20,内容,'+quotedStr('"')+')-InStr(内容,'+quotedStr('"back_pic","value":"')+')-20)';
      ExecSQL;
    end;
  end;
  if strlist.IndexOf('front_binary')=-1 then
  begin
    with qry2 do
    begin
      SQL.Text := 'ALTER TABLE content ADD COLUMN front_binary OLEObject';
      ExecSQL;
    end;
  end;
  if strlist.IndexOf('back_binary')=-1 then
  begin
    with qry2 do
    begin
      SQL.Text := 'ALTER TABLE content ADD COLUMN back_binary OLEObject';
      ExecSQL;
    end;
  end;
  if strlist.IndexOf('group_id')=-1 then
  begin
    with qry2 do
    begin
      SQL.Text := 'ALTER TABLE content ADD COLUMN group_id varchar(100)';
      ExecSQL;
      SQL.Text := 'update content set group_id='
      +'Mid(内容,InStr(内容,'+quotedStr('"group_id":"')+')+12,InStr(InStr(内容,'+quotedStr('"group_id":"')+')+12,内容,'+quotedStr('"')+')-InStr(内容,'+quotedStr('"group_id":"')+')-12)';
      ExecSQL;
    end;
  end;
  if strlist.IndexOf('group_name')=-1 then
  begin
    with qry2 do
    begin
      SQL.Text := 'ALTER TABLE content ADD COLUMN group_name varchar(100)';
      ExecSQL;
    end;
  end;
  strlist.Free;
  strlist := TStringList.Create;
  con1.GetTableNames(strlist);
  //判断识别分类表是否存在
  if strlist.IndexOf('tcardsgroup')=-1 then
  begin
    with qry2 do
    begin
      SQL.Text := 'CREATE TABLE tcardsgroup('
      +'gid varchar(50) primary key,group_name varchar(100),timecreate varchar(50),front_binary OLEObject,back_binary OLEObject)';
      ExecSQL;
    end;
  end;
  //判断识别结果表是否存在
  if strlist.IndexOf('tcards')=-1 then
  begin
    with qry2 do
    begin
      SQL.Text := 'CREATE TABLE tcards('
      +'contact_id varchar(50) primary key,timecreate varchar(50),username varchar(100),mobile varchar(200),'
      +'tel varchar(200),fax varchar(200),email varchar(200),website varchar(200),'
      +'qq varchar(200),country varchar(100),state varchar(100),city varchar(100),'
      +'street1 varchar(250),street2 varchar(200),zip varchar(50),company varchar(250),'
      +'department varchar(200),title varchar(200),jsonstr memo,jsonstrback memo,ocrtext memo,ocrtextback memo,'
      +'gid varchar(50),group_name varchar(100),'
      +'front_binary OLEObject,back_binary OLEObject)';
      ExecSQL;
    end;
  end;
  strlist.Free;
  if qry1.Active then
  qry1.Close;
  qry1.SQL.Text := 'select * from content order by mergnum,timecreate';
  qry1.Open;
  if qry3.active then
  qry3.Close;
  qry3.Open;
  btn2.Enabled := true;
  qry4.Close;
  qry4.Open;
end;

procedure TfrmCardmap.btn2Click(Sender: TObject);
var
  pathstr : string;
begin
  if FileOpenDialog1.Execute  then
  begin
    pathstr := FileOpenDialog1.Files[0];
    edtpicpath.Text := pathstr;
    btnsaveimg.Enabled := true;
  end
  else
    exit;
end;

procedure TfrmCardmap.btn3Click(Sender: TObject);
begin
  with qry2 do
  begin
    sql.Text := 'update content set onlyocrthis=1';
    ExecSQL;
  end;
  qry1.Close;
  qry1.Open;
end;

procedure TfrmCardmap.btn4Click(Sender: TObject);
begin
  with qry2 do
  begin
    sql.Text := 'update content set onlyocrthis=0';
    ExecSQL;
  end;
  qry1.Close;
  qry1.Open;
end;

procedure TfrmCardmap.btn5Click(Sender: TObject);
var
  mergnum : integer;
  i : Integer;
begin
  mergnum := 0;
  qry1.First;
  i := 1;
  while not qry1.Eof do
  begin
    if qry1.FieldByName('onlyocrthis').AsBoolean then
    begin
      mergnum := i;
      qry2.SQL.Text := 'update content set mergnum='+inttostr(mergnum)+' where id='+qry1.FieldByName('id').AsString;
      qry2.ExecSQL;
      mergnum := 0;
    end
    else
    begin
      if mergnum=0 then
      begin
        mergnum := i;
        qry2.SQL.Text := 'update content set mergnum='+inttostr(mergnum)+' where id='+qry1.FieldByName('id').AsString;
        qry2.ExecSQL;
      end
      else
      begin
        qry2.SQL.Text := 'update content set mergnum='+inttostr(mergnum)+' where id='+qry1.FieldByName('id').AsString;
        qry2.ExecSQL;
        mergnum := 0;
      end;
    end;
    i := i+1;
    qry1.Next;
  end;
  qry1.Close;
  qry1.Open;
end;

procedure TfrmCardmap.btn7Click(Sender: TObject);
begin
  ShowMessage(qry1.Filter);
  {
  with qry2 do
  begin
    SQL.Text := 'update ';
    ExecSQL;
  end;
  }
end;

procedure TfrmCardmap.btn8Click(Sender: TObject);
var
  group_name : String;
  last_gname :string;
  i : integer;
  isgroup : Boolean;
begin
  with qry2 do
  begin
    close;
    sql.Text := 'select id from content where donotorc=-1 and group_name='+quotedStr('');
    open;
  end;
  if qry2.RecordCount>0 then
  begin
    ShowMessage('存在分组名称没有设置的记录,请完善这些信息后再试');
    exit;
  end;
  qry1.First;
  while not qry1.eof do
  begin
    isgroup := qry1.FieldByName('donotorc').AsBoolean;
    group_name := qry1.FieldByName('group_name').AsString;
    if isgroup then
    begin
      last_gname := group_name;
    end
    else
    begin
      if last_gname<>'' then
      begin
        with qry2 do
        begin
          close;
          sql.Text := 'update content set group_name='+quotedStr(last_gname)+' where id='+qry1.FieldByName('id').AsString;
          ExecSQL;
        end;
      end;
    end;
    qry1.next;
  end;
  qry1.Close;
  qry1.Open;
end;

procedure TfrmCardmap.btn9Click(Sender: TObject);
var
  WicImg: TWICImage;
  Factory: IWICImagingFactory;
  FlipRotator: IWICBitmapFlipRotator;
  ms : TMemoryStream;
begin
  with qry2 do
  begin
    close;
    sql.Text := 'select * from content where donotorc=-1';
    Open;
    while not Eof do
    begin
      ms := TMemoryStream.Create;
      TBlobField(qry2.FieldByName('front_binary')).SaveToStream(ms);
      WicImg := TWICImage.Create;
      WicImg.LoadFromStream(ms);
      ms.Free;
      Factory := WicImg.ImagingFactory;
      Factory.CreateBitmapFlipRotator(FlipRotator);
      FlipRotator.Initialize(WicImg.Handle, WICBitmapTransformRotate180);
      WicImg.Handle := IWicBitmap(FlipRotator);
      ms := TMemoryStream.Create;
      WicImg.SaveToStream(ms);
      ms.Position := 0;

      qry21.Close;
      qry21.SQL.text := 'update content set front_binary=:front_binary where id='+qry2.FieldByName('id').AsString;
      qry21.Parameters.ParamByName('front_binary').LoadFromStream(ms,ftBlob);
      qry21.ExecSQL;

      FlipRotator := nil;
      Factory := nil;
      WicImg.Free;
      ms.Free;
      Next;
    end;
  end;

  ShowMessage('已将勾选"分组标记"的记录，图片角度旋转了180度');
  qry1.Close;
  qry1.Open;
end;

procedure TfrmCardmap.btnsaveimgClick(Sender: TObject);
var
Ms:TmemoryStream;
jpg1:Tjpegimage;
Image1 : Timage;
path,pic:string;
WicImg: TWICImage;
Factory: IWICImagingFactory;
FlipRotator: IWICBitmapFlipRotator;
hr: HRESULT;
angle : string;
begin
  qry1.First;
  while not qry1.eof do
  begin
    path := edtpicpath.Text+'\';
    //正面图片
    ms:=TmemoryStream.Create;
    pic := qry1.FieldByName('front_pic').AsString;
    if FileExists(path+pic) then
    begin
      angle := qry1.FieldByName('front_angle').AsString;
      if angle='180' then
      begin
        WicImg := TWICImage.Create;
        WicImg.LoadFromFile(path+pic);
        Factory := WicImg.ImagingFactory;
        Factory.CreateBitmapFlipRotator(FlipRotator);
        FlipRotator.Initialize(WicImg.Handle, WICBitmapTransformRotate180);
        WicImg.Handle := IWicBitmap(FlipRotator);
        WicImg.SaveToStream(ms);
        //Canvas.Draw(0, 0, WicImg);
        FlipRotator := nil;
        Factory := nil;
        WicImg.Free;
      end
      else if angle='90' then
      begin
        WicImg := TWICImage.Create;
        WicImg.LoadFromFile(path+pic);
        Factory := WicImg.ImagingFactory;
        Factory.CreateBitmapFlipRotator(FlipRotator);
        FlipRotator.Initialize(WicImg.Handle, WICBitmapTransformRotate270);
        WicImg.Handle := IWicBitmap(FlipRotator);
        WicImg.SaveToStream(ms);
        //Canvas.Draw(0, 0, WicImg);
        FlipRotator := nil;
        Factory := nil;
        WicImg.Free;
      end
      else if angle='270' then
      begin
        WicImg := TWICImage.Create;
        WicImg.LoadFromFile(path+pic);
        Factory := WicImg.ImagingFactory;
        Factory.CreateBitmapFlipRotator(FlipRotator);
        FlipRotator.Initialize(WicImg.Handle, WICBitmapTransformRotate90);
        WicImg.Handle := IWicBitmap(FlipRotator);
        WicImg.SaveToStream(ms);
        //Canvas.Draw(0, 0, WicImg);
        FlipRotator := nil;
        Factory := nil;
        WicImg.Free;
      end
      else
        ms.LoadFromFile(path+pic);
      ms.Position :=0;
      with qry2 do
      begin
        sql.Text := 'update content set front_binary=:front_binary where id='+qry1.FieldByName('id').AsString;
        Parameters.ParamByName('front_binary').LoadFromStream(ms,ftBlob);
        ExecSQL;
      end;
      Ms.Free;
    end;
    //反面图片
    ms:=TmemoryStream.Create;
    pic := qry1.FieldByName('back_pic').AsString;
    if FileExists(path+pic) then
    begin
      ms.LoadFromFile(path+pic);
      ms.Position :=0;
      with qry2 do
      begin
        sql.Text := 'update content set back_binary=:back_binary where id='+qry1.FieldByName('id').AsString;
        Parameters.ParamByName('back_binary').LoadFromStream(ms,ftBlob);
        ExecSQL;
      end;
      Ms.Free;
    end;
    qry1.next;
  end;
  qry1.close;
  qry1.Open;
end;

procedure TfrmCardmap.cxGrid1DBTableView1CustomDrawIndicatorCell(
  Sender: TcxGridTableView; ACanvas: TcxCanvas;
  AViewInfo: TcxCustomGridIndicatorItemViewInfo; var ADone: Boolean);
var
  FValue: string;
  FBounds: TRect;
begin

end;

procedure TfrmCardmap.cxRadioButton1Click(Sender: TObject);
begin
  cxGrid1DBCardView1.LayoutDirection := ldHorizontal;
end;

procedure TfrmCardmap.cxRadioButton2Click(Sender: TObject);
begin
  cxGrid1DBCardView1.LayoutDirection := ldVertical;
end;

procedure TfrmCardmap.edtpicpathChange(Sender: TObject);
begin
  btnsaveimg.Enabled := true;
end;

procedure TfrmCardmap.qry1AfterOpen(DataSet: TDataSet);
begin
  lblqry1cnt.Caption := IntToStr(qry1.RecordCount);
end;

end.
